package com.foreveredu.propms.persist.hibernate;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;

import com.foreveredu.propms.domain.House;
import com.foreveredu.propms.domain.Owner;
import com.foreveredu.propms.domain.PageModel;
import com.foreveredu.propms.domain.SubDistrict;
import com.foreveredu.propms.persist.IOwnerDao;

public class HibernateOwnerDao extends HibernateAbstractDao<Owner, Integer>
		implements IOwnerDao {

	private static final long serialVersionUID = 1L;

	public HibernateOwnerDao() {
		super(Owner.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public Owner findByOwnerId(String ownerId) {
		List<Owner> ownerList = this.getHibernateTemplate().find(
				"from Owner o where o.ownerId= ?", ownerId);
		Owner owner = null;
		if (ownerList != null && ownerList.size() > 0) {
			owner = ownerList.get(0);
		}
		return owner;
	}

	@Override
	public PageModel findOwnerList(int offset) {

		PageModel pm = new PageModel();
		Query query = this.getSession().createQuery(
				"select count(*) from Owner as n");
		Object count = query.uniqueResult();
		if (count != null) {
			int total = Integer.parseInt(count.toString());
			pm.setTotal(total);
		}

		if (offset + 10 > pm.getTotal()) {
			Query query2 = this.getSession().createQuery("from Owner as n");
			query2.setFirstResult(offset);
			query2.setMaxResults(offset + 10 - pm.getTotal());
			pm.setDatas(query2.list());
		}
		Query query3 = this.getSession().createQuery("from Owner as n");
		query3.setFirstResult(offset);
		query3.setMaxResults(10);
		pm.setDatas(query3.list());
		return pm;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Owner findOwnerByIdCard(String idCard) {

		List<Owner> ownerList = this.getHibernateTemplate().find("from Owner o where o.idCard= ?", idCard);
		Owner owner = null;
		if (ownerList != null && ownerList.size() > 0) {
			owner = ownerList.get(0);
		}
		return owner;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Owner findByName(String ownerName) {
		List<Owner> ownerList = this.getHibernateTemplate().find("from Owner o where o.name= ?", ownerName);
		Owner owner = null;
		if (ownerList != null && ownerList.size() > 0) {
			owner = ownerList.get(0);
		}
		return owner;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Owner findByPhone(String phone) {
		List<Owner> ownerList = this.getHibernateTemplate().find("from Owner o where o.phone= ?", phone);
		Owner owner = null;
		if (ownerList != null && ownerList.size() > 0) {
			owner = ownerList.get(0);
		}
		return owner;
	}

	@Override
	public PageModel findOwnerListByDistrict(int offset, int districtId) {
		PageModel pm = new PageModel();
		Query query = this.getSession().createQuery("select count(*) from House as h where h.building.subDistrict.district.id = ? ");
		query.setInteger(0, districtId);
		Object count = query.uniqueResult();
		if (count != null) {
			int total = Integer.parseInt(count.toString());
			pm.setTotal(total);
		}

		if (offset + 10 > pm.getTotal()) {
			Query query2 = this.getSession().createQuery("from House as h where h.building.subDistrict.district.id = ? ");
			query2.setInteger(0, districtId);
			query2.setFirstResult(offset);
			query2.setMaxResults(offset + 10 - pm.getTotal());
			pm.setDatas(query2.list());
		}
		Query query3 = this.getSession().createQuery("from House as h where h.building.subDistrict.district.id = ?" );
		query3.setInteger(0, districtId);
		query3.setFirstResult(offset);
		query3.setMaxResults(10);
		pm.setDatas(query3.list());
		return pm;
	}

	@Override
	public PageModel findOwnerListBySubDistrict(int offset, int subDistrict_id) {
		PageModel pm = new PageModel();
		Query query = this.getSession().createQuery("select count(*) from House as h where h.building.subDistrict.id = ? ");
		query.setInteger(0, subDistrict_id);
		Object count = query.uniqueResult();
		if (count != null) {
			int total = Integer.parseInt(count.toString());
			pm.setTotal(total);
		}

		if (offset + 10 > pm.getTotal()) {
			Query query2 = this.getSession().createQuery("from House as h where h.building.subDistrict.id = ? ");
			query2.setInteger(0, subDistrict_id);
			query2.setFirstResult(offset);
			query2.setMaxResults(offset + 10 - pm.getTotal());
			pm.setDatas(query2.list());
		}
		Query query3 = this.getSession().createQuery("from House as h where h.building.subDistrict.id = ?" );
		query3.setInteger(0, subDistrict_id);
		query3.setFirstResult(offset);
		query3.setMaxResults(10);
		pm.setDatas(query3.list());
		return pm;
	}

	@Override
	public PageModel findOweCollectChargeBySub(int offset,int subDistrict_id,int year,int month) {
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		Connection conn = this.getSession().connection();
		CallableStatement cstmt;
		try {
			cstmt = conn.prepareCall("{call proc_queryOwnCollectChargeBySub(?,?,?)}");
			cstmt.setInt(1, subDistrict_id);
			cstmt.setInt(2, year);
			cstmt.setInt(3, month);
//			cstmt.registerOutParameter(4, Types.VARCHAR);
//			cstmt.registerOutParameter(5, Types.VARCHAR);
//			cstmt.registerOutParameter(6, Types.FLOAT);
//			cstmt.registerOutParameter(7, Types.FLOAT);	
							
			cstmt.execute();
			ResultSet rs = cstmt.getResultSet();
			while(rs.next()){
				Map<String, Object> queryMap = new HashMap<String, Object>();
				queryMap.put("subName", rs.getString(1));
				queryMap.put("oweName", rs.getString(2));
				queryMap.put("amount", rs.getFloat(3));
				queryMap.put("paidAmount", rs.getFloat(4));
				list.add(queryMap);
			}
			System.out.println(list.size()+"数据记录数");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println(offset+"页码");
		PageModel pm = new PageModel();
		if (list != null && list.size() > 0) {
			pm.setTotal(list.size());
			if (offset + 10 > list.size()) {
				pm.setDatas(list.subList(offset, list.size()));
				return pm;
			}
			pm.setDatas(list.subList(offset, offset + 10));
		}
		return pm;
	}

	@Override
	public PageModel findOweCollectChargeAll(int offset, int year, int month) {
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		Connection conn = this.getSession().connection();
		CallableStatement cstmt;
		try {
			cstmt = conn.prepareCall("{call proc_queryOwnCollectCharge(?,?)}");
			cstmt.setInt(1, year);
			cstmt.setInt(2, month);
//			cstmt.registerOutParameter(4, Types.VARCHAR);
//			cstmt.registerOutParameter(5, Types.VARCHAR);
//			cstmt.registerOutParameter(6, Types.FLOAT);
//			cstmt.registerOutParameter(7, Types.FLOAT);	
							
			cstmt.execute();
			ResultSet rs = cstmt.getResultSet();
			while(rs.next()){
				Map<String, Object> queryMap = new HashMap<String, Object>();
				queryMap.put("subName", rs.getString(1));
				queryMap.put("oweName", rs.getString(2));
				queryMap.put("amount", rs.getFloat(3));
				queryMap.put("paidAmount", rs.getFloat(4));
				list.add(queryMap);
			}
			System.out.println(list.size()+"数据记录数");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println(offset+"页码");
		PageModel pm = new PageModel();
		if (list != null && list.size() > 0) {
			pm.setTotal(list.size());
			if (offset + 10 > list.size()) {
				pm.setDatas(list.subList(offset, list.size()));
				return pm;
			}
			pm.setDatas(list.subList(offset, offset + 10));
		}
		return pm;
	}

	@Override
	public PageModel findOwdCollectChargeBySubList(int offset,SubDistrict sub, int year, int month) {
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		Connection conn = this.getSession().connection();
		CallableStatement cstmt;
		List<Map<String, Object>> houseList = new ArrayList<Map<String,Object>>();
		try {
			cstmt = conn.prepareCall("{call proc_queryOwnCollectChargeHouse(?,?,?)}");
			cstmt.setInt(1, sub.getId());
			cstmt.setInt(2, year);
			cstmt.setInt(3, month);
			cstmt.execute();
			ResultSet rs = cstmt.getResultSet();
			while(rs.next()){
				Map<String, Object> queryMap = new HashMap<String, Object>();
				queryMap.put("houseId", rs.getString(1));
				queryMap.put("houseName", rs.getString(2));
				houseList.add(queryMap);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(houseList.size()>0){
			try {
				for(int i = 0;i<houseList.size();i++){
					cstmt = conn.prepareCall("{call proc_queryOwnCollectChargeList(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
					cstmt.setInt(1, Integer.parseInt(houseList.get(i).get("houseId").toString()));
					cstmt.setInt(2, year);
					cstmt.setInt(3, month);
					cstmt.registerOutParameter(4, Types.FLOAT);
					cstmt.registerOutParameter(5, Types.FLOAT);
					cstmt.registerOutParameter(6, Types.FLOAT);
					cstmt.registerOutParameter(7, Types.FLOAT);
					cstmt.registerOutParameter(8, Types.FLOAT);
					cstmt.registerOutParameter(9, Types.FLOAT);
					cstmt.registerOutParameter(10, Types.FLOAT);
					cstmt.registerOutParameter(11, Types.FLOAT);
					cstmt.registerOutParameter(12, Types.FLOAT);
					cstmt.registerOutParameter(13, Types.FLOAT);
					cstmt.registerOutParameter(14, Types.FLOAT);
					cstmt.registerOutParameter(15, Types.FLOAT);
					cstmt.registerOutParameter(16, Types.FLOAT);
					cstmt.registerOutParameter(17, Types.FLOAT);
					cstmt.registerOutParameter(18, Types.FLOAT);
					cstmt.registerOutParameter(19, Types.FLOAT);
					cstmt.execute();
					Map<String, Object> queryMap = new HashMap<String, Object>();
					queryMap.put("subName", sub.getName());
					queryMap.put("oweName", houseList.get(i).get("houseName"));
					queryMap.put("amount4", cstmt.getFloat(4));
					queryMap.put("amount5", cstmt.getFloat(5));
					queryMap.put("amount6", cstmt.getFloat(6));
					queryMap.put("amount7", cstmt.getFloat(7));
					queryMap.put("amount8", cstmt.getFloat(8));
					queryMap.put("amount9", cstmt.getFloat(9));
					queryMap.put("amount10", cstmt.getFloat(10));
					queryMap.put("amount11", cstmt.getFloat(11));
					queryMap.put("amount12", cstmt.getFloat(12));
					queryMap.put("amount13", cstmt.getFloat(13));
					queryMap.put("amount14", cstmt.getFloat(14));
					queryMap.put("amount15", cstmt.getFloat(15));
					queryMap.put("amount16", cstmt.getFloat(16));
					queryMap.put("amount17", cstmt.getFloat(17));
					queryMap.put("amount18", cstmt.getFloat(18));
					queryMap.put("amount19", cstmt.getFloat(19));
					list.add(queryMap);
				}
				System.out.println(list.size()+"数据记录数");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	
	
		
		System.out.println(offset+"页码");
		PageModel pm = new PageModel();
		if (list != null && list.size() > 0) {
			pm.setTotal(list.size());
			if (offset + 10 > list.size()) {
				pm.setDatas(list.subList(offset, list.size()));
				return pm;
			}
			pm.setDatas(list.subList(offset, offset + 10));
		}
		return pm;
	}

	@Override
	public PageModel findOweCollectChargeAllList(int offset, int year, int month) {
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		Connection conn = this.getSession().connection();
		CallableStatement cstmt;
		List<Map<String, Object>> houseList = new ArrayList<Map<String,Object>>();
		try {
			cstmt = conn.prepareCall("{call proc_queryOwnCollectChargeAllHouse(?,?)}");
			cstmt.setInt(1, year);
			cstmt.setInt(2, month);
			cstmt.execute();
			ResultSet rs = cstmt.getResultSet();
			while(rs.next()){
				Map<String, Object> queryMap = new HashMap<String, Object>();
				queryMap.put("subName", rs.getString(1));
				queryMap.put("houseId", rs.getString(2));
				queryMap.put("houseName", rs.getString(3));
				houseList.add(queryMap);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(houseList.size()>0){
			try {
				for(int i = 0;i<houseList.size();i++){
					cstmt = conn.prepareCall("{call proc_queryOwnCollectChargeList(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
					cstmt.setInt(1, Integer.parseInt(houseList.get(i).get("houseId").toString()));
					cstmt.setInt(2, year);
					cstmt.setInt(3, month);
					cstmt.registerOutParameter(4, Types.FLOAT);
					cstmt.registerOutParameter(5, Types.FLOAT);
					cstmt.registerOutParameter(6, Types.FLOAT);
					cstmt.registerOutParameter(7, Types.FLOAT);
					cstmt.registerOutParameter(8, Types.FLOAT);
					cstmt.registerOutParameter(9, Types.FLOAT);
					cstmt.registerOutParameter(10, Types.FLOAT);
					cstmt.registerOutParameter(11, Types.FLOAT);
					cstmt.registerOutParameter(12, Types.FLOAT);
					cstmt.registerOutParameter(13, Types.FLOAT);
					cstmt.registerOutParameter(14, Types.FLOAT);
					cstmt.registerOutParameter(15, Types.FLOAT);
					cstmt.registerOutParameter(16, Types.FLOAT);
					cstmt.registerOutParameter(17, Types.FLOAT);
					cstmt.registerOutParameter(18, Types.FLOAT);
					cstmt.registerOutParameter(19, Types.FLOAT);
					cstmt.execute();
					Map<String, Object> queryMap = new HashMap<String, Object>();
					queryMap.put("subName", houseList.get(i).get("subName"));
					queryMap.put("oweName", houseList.get(i).get("houseName"));
					queryMap.put("amount4", cstmt.getFloat(4));
					queryMap.put("amount5", cstmt.getFloat(5));
					queryMap.put("amount6", cstmt.getFloat(6));
					queryMap.put("amount7", cstmt.getFloat(7));
					queryMap.put("amount8", cstmt.getFloat(8));
					queryMap.put("amount9", cstmt.getFloat(9));
					queryMap.put("amount10", cstmt.getFloat(10));
					queryMap.put("amount11", cstmt.getFloat(11));
					queryMap.put("amount12", cstmt.getFloat(12));
					queryMap.put("amount13", cstmt.getFloat(13));
					queryMap.put("amount14", cstmt.getFloat(14));
					queryMap.put("amount15", cstmt.getFloat(15));
					queryMap.put("amount16", cstmt.getFloat(16));
					queryMap.put("amount17", cstmt.getFloat(17));
					queryMap.put("amount18", cstmt.getFloat(18));
					queryMap.put("amount19", cstmt.getFloat(19));
					list.add(queryMap);
				}
				System.out.println(list.size()+"数据记录数");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	
	
		
		System.out.println(offset+"页码");
		PageModel pm = new PageModel();
		if (list != null && list.size() > 0) {
			pm.setTotal(list.size());
			if (offset + 10 > list.size()) {
				pm.setDatas(list.subList(offset, list.size()));
				return pm;
			}
			pm.setDatas(list.subList(offset, offset + 10));
		}
		return pm;
	}

	@Override
	public List<Map<String, Object>> findOweCollectChargeAll(int year, int month) {
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		Connection conn = this.getSession().connection();
		CallableStatement cstmt;
		try {
			cstmt = conn.prepareCall("{call proc_queryOwnCollectCharge(?,?)}");
			cstmt.setInt(1, year);
			cstmt.setInt(2, month);	
			cstmt.execute();
			ResultSet rs = cstmt.getResultSet();
			while(rs.next()){
				Map<String, Object> queryMap = new HashMap<String, Object>();
				queryMap.put("subName", rs.getString(1));
				queryMap.put("oweName", rs.getString(2));
				queryMap.put("amount", rs.getFloat(3));
				queryMap.put("paidAmount", rs.getFloat(4));
				queryMap.put("amount", rs.getFloat(3)-rs.getFloat(4));
				list.add(queryMap);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Map<String, Object>> findOweCollectChargeBySub(int id,
			int year, int month) {
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		Connection conn = this.getSession().connection();
		CallableStatement cstmt;
		try {
			cstmt = conn.prepareCall("{call proc_queryOwnCollectChargeBySub(?,?,?)}");
			cstmt.setInt(1, id);
			cstmt.setInt(2, year);
			cstmt.setInt(3, month);
			cstmt.execute();
			ResultSet rs = cstmt.getResultSet();
			while(rs.next()){
				Map<String, Object> queryMap = new HashMap<String, Object>();
				queryMap.put("subName", rs.getString(1));
				queryMap.put("oweName", rs.getString(2));
				queryMap.put("amount", rs.getFloat(3));
				queryMap.put("paidAmount", rs.getFloat(4));
				queryMap.put("amount", rs.getFloat(3)-rs.getFloat(4));
				list.add(queryMap);
			}
			System.out.println(list.size()+"数据记录数");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Map<String, Object>> findOwdCollectChargeBySubList(
			SubDistrict sub, int year, int month) {
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		Connection conn = this.getSession().connection();
		CallableStatement cstmt;
		List<Map<String, Object>> houseList = new ArrayList<Map<String,Object>>();
		try {
			cstmt = conn.prepareCall("{call proc_queryOwnCollectChargeHouse(?,?,?)}");
			cstmt.setInt(1, sub.getId());
			cstmt.setInt(2, year);
			cstmt.setInt(3, month);
			cstmt.execute();
			ResultSet rs = cstmt.getResultSet();
			while(rs.next()){
				Map<String, Object> queryMap = new HashMap<String, Object>();
				queryMap.put("houseId", rs.getString(1));
				queryMap.put("houseName", rs.getString(2));
				houseList.add(queryMap);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(houseList.size()>0){
			try {
				for(int i = 0;i<houseList.size();i++){
					cstmt = conn.prepareCall("{call proc_queryOwnCollectChargeList(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
					cstmt.setInt(1, Integer.parseInt(houseList.get(i).get("houseId").toString()));
					cstmt.setInt(2, year);
					cstmt.setInt(3, month);
					cstmt.registerOutParameter(4, Types.FLOAT);
					cstmt.registerOutParameter(5, Types.FLOAT);
					cstmt.registerOutParameter(6, Types.FLOAT);
					cstmt.registerOutParameter(7, Types.FLOAT);
					cstmt.registerOutParameter(8, Types.FLOAT);
					cstmt.registerOutParameter(9, Types.FLOAT);
					cstmt.registerOutParameter(10, Types.FLOAT);
					cstmt.registerOutParameter(11, Types.FLOAT);
					cstmt.registerOutParameter(12, Types.FLOAT);
					cstmt.registerOutParameter(13, Types.FLOAT);
					cstmt.registerOutParameter(14, Types.FLOAT);
					cstmt.registerOutParameter(15, Types.FLOAT);
					cstmt.registerOutParameter(16, Types.FLOAT);
					cstmt.registerOutParameter(17, Types.FLOAT);
					cstmt.registerOutParameter(18, Types.FLOAT);
					cstmt.registerOutParameter(19, Types.FLOAT);
					cstmt.execute();
					Map<String, Object> queryMap = new HashMap<String, Object>();
					queryMap.put("subName", sub.getName());
					queryMap.put("oweName", houseList.get(i).get("houseName"));
					queryMap.put("amount4", cstmt.getFloat(4));
					queryMap.put("amount5", cstmt.getFloat(5));
					queryMap.put("amount6", cstmt.getFloat(6));
					queryMap.put("amount7", cstmt.getFloat(7));
					queryMap.put("amount8", cstmt.getFloat(8));
					queryMap.put("amount9", cstmt.getFloat(9));
					queryMap.put("amount10", cstmt.getFloat(10));
					queryMap.put("amount11", cstmt.getFloat(11));
					queryMap.put("amount12", cstmt.getFloat(12));
					queryMap.put("amount13", cstmt.getFloat(13));
					queryMap.put("amount14", cstmt.getFloat(14));
					queryMap.put("amount15", cstmt.getFloat(15));
					queryMap.put("amount16", cstmt.getFloat(16));
					queryMap.put("amount17", cstmt.getFloat(17));
					queryMap.put("amount18", cstmt.getFloat(18));
					queryMap.put("amount19", cstmt.getFloat(19));
					queryMap.put("amount20", cstmt.getFloat(4)+cstmt.getFloat(5)+cstmt.getFloat(6)+cstmt.getFloat(7)+cstmt.getFloat(8)+cstmt.getFloat(9)+cstmt.getFloat(10)+cstmt.getFloat(11)+cstmt.getFloat(12)+cstmt.getFloat(13)+cstmt.getFloat(14)+cstmt.getFloat(15)+cstmt.getFloat(16)+cstmt.getFloat(17)+cstmt.getFloat(18)+cstmt.getFloat(19));
					list.add(queryMap);
				}
				System.out.println(list.size()+"数据记录数");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	
	
		
		
		return list;
	}

	@Override
	public List<Map<String, Object>> findOweCollectChargeAllList(int year,
			int month) {
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		Connection conn = this.getSession().connection();
		CallableStatement cstmt;
		List<Map<String, Object>> houseList = new ArrayList<Map<String,Object>>();
		try {
			cstmt = conn.prepareCall("{call proc_queryOwnCollectChargeAllHouse(?,?)}");
			cstmt.setInt(1, year);
			cstmt.setInt(2, month);
			cstmt.execute();
			ResultSet rs = cstmt.getResultSet();
			while(rs.next()){
				Map<String, Object> queryMap = new HashMap<String, Object>();
				queryMap.put("subName", rs.getString(1));
				queryMap.put("houseId", rs.getString(2));
				queryMap.put("houseName", rs.getString(3));
				houseList.add(queryMap);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(houseList.size()>0){
			try {
				for(int i = 0;i<houseList.size();i++){
					cstmt = conn.prepareCall("{call proc_queryOwnCollectChargeList(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
					cstmt.setInt(1, Integer.parseInt(houseList.get(i).get("houseId").toString()));
					cstmt.setInt(2, year);
					cstmt.setInt(3, month);
					cstmt.registerOutParameter(4, Types.FLOAT);
					cstmt.registerOutParameter(5, Types.FLOAT);
					cstmt.registerOutParameter(6, Types.FLOAT);
					cstmt.registerOutParameter(7, Types.FLOAT);
					cstmt.registerOutParameter(8, Types.FLOAT);
					cstmt.registerOutParameter(9, Types.FLOAT);
					cstmt.registerOutParameter(10, Types.FLOAT);
					cstmt.registerOutParameter(11, Types.FLOAT);
					cstmt.registerOutParameter(12, Types.FLOAT);
					cstmt.registerOutParameter(13, Types.FLOAT);
					cstmt.registerOutParameter(14, Types.FLOAT);
					cstmt.registerOutParameter(15, Types.FLOAT);
					cstmt.registerOutParameter(16, Types.FLOAT);
					cstmt.registerOutParameter(17, Types.FLOAT);
					cstmt.registerOutParameter(18, Types.FLOAT);
					cstmt.registerOutParameter(19, Types.FLOAT);
					cstmt.execute();
					Map<String, Object> queryMap = new HashMap<String, Object>();
					queryMap.put("subName", houseList.get(i).get("subName"));
					queryMap.put("oweName", houseList.get(i).get("houseName"));
					queryMap.put("amount4", cstmt.getFloat(4));
					queryMap.put("amount5", cstmt.getFloat(5));
					queryMap.put("amount6", cstmt.getFloat(6));
					queryMap.put("amount7", cstmt.getFloat(7));
					queryMap.put("amount8", cstmt.getFloat(8));
					queryMap.put("amount9", cstmt.getFloat(9));
					queryMap.put("amount10", cstmt.getFloat(10));
					queryMap.put("amount11", cstmt.getFloat(11));
					queryMap.put("amount12", cstmt.getFloat(12));
					queryMap.put("amount13", cstmt.getFloat(13));
					queryMap.put("amount14", cstmt.getFloat(14));
					queryMap.put("amount15", cstmt.getFloat(15));
					queryMap.put("amount16", cstmt.getFloat(16));
					queryMap.put("amount17", cstmt.getFloat(17));
					queryMap.put("amount18", cstmt.getFloat(18));
					queryMap.put("amount19", cstmt.getFloat(19));
					queryMap.put("amount20", cstmt.getFloat(4)+cstmt.getFloat(5)+cstmt.getFloat(6)+cstmt.getFloat(7)+cstmt.getFloat(8)+cstmt.getFloat(9)+cstmt.getFloat(10)+cstmt.getFloat(11)+cstmt.getFloat(12)+cstmt.getFloat(13)+cstmt.getFloat(14)+cstmt.getFloat(15)+cstmt.getFloat(16)+cstmt.getFloat(17)+cstmt.getFloat(18)+cstmt.getFloat(19));
					list.add(queryMap);
				}
				System.out.println(list.size()+"数据记录数");
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Owner findOwnerByHouseIdAndIdCard(String houseId, String idCard) {
		List<House> houseList = this.getHibernateTemplate().find("from House as h where h.houseId = ? and h.owner.idCard = ?", new Object[]{houseId, idCard});
		House house = null;
		Owner owner =null;
		if (houseList != null && houseList.size() > 0) {
			house = houseList.get(0);
			owner =house.getOwner();
		}
		
		return owner;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Owner findOwnerByHouseIdAndPassword(String houseId, String password) {
		List<House> houseList = this.getHibernateTemplate().find("from House as h where h.houseId = ? and h.owner.password = ?", new Object[]{houseId, password});
		House house = null;
		Owner owner =null;
		if (houseList != null && houseList.size() > 0) {
			house = houseList.get(0);
			owner =house.getOwner();
		}
		
		return owner;
	}

}
